﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Exhibition.Common.Meta;
using Exhibition.Common.Extension;
namespace Exhibition.Common.Helper
{
    public class DBHelper
    {
        public static Database Db
        {
            get { 
                return DatabaseFactory.CreateDatabase("DB"); 
            }
        }

 

        

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="sql">T-SQL查询语句</param>
        /// <param name="currentIndex">当前页号（从0开始）</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>IDataReader</returns>
        public static IDataReader GetPageData(string sqlStr, int pageIndex, int pageSize, out int rowCount)
        {
            DbCommand cmd = Db.GetSqlStringCommand(@"declare @p1 int

                                                    declare @currentPage int
                                                    set @currentPage = 0 
                                                    declare @RowCount int
                                                    set @RowCount = 0 
                                                    declare @PageCount int
                                                    set @PageCount = 0 

                                                    exec sp_cursoropen 
                                                    @p1 output,@strSql,
                                                    @scrollopt=1,
                                                    @ccopt=1,
                                                    @rowcount=@rowCount 
                                                    output --得到总记录数

                                                    select @PageCount=ceiling(1.0*@rowCount/@pagesize)  --得到总页数
                                                    ,@currentPage=(@PageIndex-1)*@PageSize+1
                                                    select @RowCount,@PageCount
                                                    exec sp_cursorfetch @p1,16,@currentPage,@PageSize 
                                                    exec sp_cursorclose @p1");

            Db.AddInParameter(cmd, "StrSql", DbType.AnsiString, sqlStr);
            Db.AddInParameter(cmd, "PageIndex", DbType.Int32, pageIndex + 1);

            Db.AddInParameter(cmd, "PageSize", DbType.Int32, pageSize);
            //  db.AddInParameter(cmd, "rowcount", DbType.Int32, 4);
            IDataReader dataReader = Db.ExecuteReader(cmd);
            if (dataReader.NextResult())
            {
                dataReader.Read();
                rowCount = dataReader.GetInt32(0);
                dataReader.NextResult();
            }
            else
            {
                rowCount = 0;
                dataReader = null;
            }
            return dataReader;
        }

        /// <summary>
        /// 将分页后的数据填充到DataTable.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="currentIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetPageDataTable( string sql, int currentIndex, int pageSize, out int rowCount)
        {
            DataTable tab = new DataTable("data");
            tab = GetDataTable(sql, currentIndex, pageSize, out rowCount);
            return tab;
        }

        public static DataTable GetDataTable(string sql, int currentIndex, int pageSize, out int rowCount)
        {
            DbCommand cmd = Db.GetStoredProcCommand("GetPageRecord");
            Db.AddInParameter(cmd, "@StrSql", DbType.String, sql);
            Db.AddInParameter(cmd, "@PageIndex", DbType.Int32, currentIndex);
            Db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);
            try
            {
                DataSet ds = Db.ExecuteDataSet(cmd);
                rowCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
                return ds.Tables[2];
            }
            catch (Exception ex)
            {
                LogHelper.Error("错误信息"+ex.StackTrace);
                throw;
            }
        }

        /// <summary>
        /// 返回DATATABLE
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="db">数据库连接</param>
        /// <param name="parameters">参数数组</param>
        /// <returns></returns>
        public static DataTable ExecuteDataSet(string sql, List<Parameter> parameters)
        {
            DataTable dataTable = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    dataTable = Db.ExecuteDataSet(dbCommand).Tables[0];

                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteDataSet(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql);
                    throw ex;
                }


            }
            return dataTable;

        }


        public static DataSet RtnExecuteDataSet(string sql, List<Parameter> parameters)
        {
            DataSet ds = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    ds = Db.ExecuteDataSet(dbCommand);

                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法RtnExecuteDataSet(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }
            }
            return ds;

        }


        /// <summary>
        /// 返回datatable 不带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataSet(string sql)
        {
            DataTable dataTable = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    dataTable = Db.ExecuteDataSet(dbCommand).Tables[0];

                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteDataSet(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }

            }
            return dataTable;

        }
        /// <summary>
        /// 返回执行查询的记录条数

        /// </summary>
        /// <param name="sql"></param>
        /// <param name="db"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, List<Parameter> parameters)
        {
            int recordcount = 0;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    recordcount = Db.ExecuteNonQuery(dbCommand);
                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteNonQuery(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }

            }
            return recordcount;
        }


        /// <summary>
        /// 返回记录条数  不带参数数组
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            int recordcount = 0;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    recordcount = Db.ExecuteNonQuery(dbCommand);
                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteNonQuery(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw;
                }

            }
            return recordcount;
        }

        public static object ExecuteScalar(string sql)
        {
            object obj = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    obj = Db.ExecuteScalar(dbCommand);
                    
                }
                catch (Exception ex)
                {
                    LogHelper.Error("方法ExecuteScalar(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql);
                    throw ex;


                }

            }
            return DataHelper.GetInteger(obj);
        }


        public static IDataReader ExecuteReader(string sql)
        {
            IDataReader obj = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    obj = Db.ExecuteReader(dbCommand);

                }
                catch (Exception ex)
                {
                    LogHelper.Error("方法ExecuteReader(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql);
                    throw ex;


                }

            }
            return obj;
        }
        public static object ExecuteScalar(string sql,  List<Parameter> parameters)
        {
            object obj = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    obj = Db.ExecuteScalar(dbCommand);
                }
                catch (System.Exception ex)
                {
                    LogHelper.Error("方法ExecuteScalar(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }

            }
            return DataHelper.GetInteger(obj);
        }

        /// <summary>
        /// 组合参数
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="dbCommand"></param>
        /// <param name="db"></param>
        public static void BuildePrm(List<Parameter> parameters, DbCommand dbCommand, Database db)
        {
            foreach (var parameter in parameters)
            {
                db.AddInParameter(dbCommand, parameter.ValuePrm, parameter.DType, parameter.Value);
            }
        }


        /// <summary>
        /// 通用删除
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static bool DeleteData(int id, string tableName)
        {
            List<Parameter> parames = new List<Parameter>() { 
                new Parameter(){
                    DType=DbType.Int32,
                    Value=id.ToString(),
                    ValuePrm="id"
                }
            };

            string sql = "update " + tableName + " set isdelete=1 where id=@id";
            try
            {
                int obj = ExecuteNonQuery(sql,  parames);
                if (obj > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                LogHelper.Error("执行SQL报错,出错SQL=" + sql + ";" + ex.Message);
                return false;
            }
        }



        /// <summary>
        /// 通用查询单条数据返回实体
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">ID</param>
        /// <param name="tableName">数据库表名</param>
        /// <returns>返回数据</returns>
        public T SelectData<T>(int id, string tableName)
        {
            List<Parameter> parames = new List<Parameter>();
            Parameter ID = new Parameter();
            ID.DType = DbType.Int32;
            ID.Value = id.ToString();
            ID.ValuePrm = "id";
            parames.Add(ID);
            string sql = "select * from " + tableName + " where id=@id";
            try
            {
                DataTable dt = DBHelper.ExecuteDataSet(sql,  parames);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return dt.ToEntity<T>();
                }
                return default(T);

            }
            catch (Exception ex)
            {
                LogHelper.Error("执行SQL报错,出错SQL=" + sql + ";" + ex.Message);
                return default(T);
            }
        }

        /// <summary>
        /// 从DataReader里读取数据将数据转换成实例列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        //public IList<T> SelectDatas<T>(int id, string tableName)
        //{
        //    List<Parameter> parames = new List<Parameter>();
        //    Parameter ID = new Parameter();
        //    ID.DType = DbType.Int32;
        //    ID.Value = id.ToString();
        //    ID.ValuePrm = "id";
        //    parames.Add(ID);
        //    string sql = "select * from " + tableName + " where id=@id";
        //    try
        //    {
        //        IDataReader dr =ExecuteReader(sql);
        //        if (dr != null && dr.Read())
        //        {
        //            return dr.ToEntityList<T>();
        //        }
        //        return null;

        //    }
        //    catch (Exception ex)
        //    {
        //        LogHelper.Error("执行SQL报错,出错SQL=" + sql + ";" + ex.Message);
        //        return null;
        //    }
        //}

        /// <summary>
        /// 通用表查询
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetCommonData(string tableName)
        {
            if (!string.IsNullOrEmpty(tableName))
            {
                string sql = "SELECT * FROM " + tableName + " where IsDelete=0";
                try
                {
                    DataTable dt = DBHelper.ExecuteDataSet(sql);
                    return dt;
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL报错,出错SQL=" + sql + ";" + ex.Message + "，注意查看表是否存在IsDelete");

                    return null;
                }
            }
            else
            {
                LogHelper.Error("没有获取装载表:" + tableName);
                return null;
            }
        }

        /// <summary>
        /// 通用查询，分页，删除
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <param name="orderString"></param>
        /// <param name="isdel"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable GetCommonData(string tableName, int rows, int page, string orderString, string isdel, out int total)
        {
            total = 0;
            string strWhr = " WHERE 1=1  ";

            if (!string.IsNullOrEmpty(tableName))
            {
                if (!string.IsNullOrEmpty(isdel))
                {
                    strWhr += " AND ( IsDelete is null or IsDelete=" + isdel + " ) ";
                }

                string sql = "SELECT * FROM " + tableName + strWhr + orderString;
                try
                {
                    DataTable dt = DBHelper.GetPageDataTable(sql.ToString(), ++page, rows, out total);
                    return dt;
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL报错,出错SQL=" + sql + ";" + ex.Message); 
                    return null;
                }
            }
            else
            {
                LogHelper.Error("没有获取装载表:" + tableName);
                return null;
            }
        }

        // <summary>
        /// 获取一条数据，如果存在多条则只返回第一条数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="where">过滤条件</param>
        /// <returns></returns>
        public DataTable GetSingleData(string tableName, string where)
        {
            if (!string.IsNullOrEmpty(tableName))
            {
                string sql = "SELECT top 1 * FROM " + tableName + " where IsDelete=0" + where;
                try
                {
                    DataTable dt = DBHelper.ExecuteDataSet(sql);
                    return dt;
                }
                catch (Exception ex)
                {
                    LogHelper.Error("执行SQL报错,出错SQL=" + sql + ";" + ex.Message);

                    return null;
                }
            }
            else
            {
                LogHelper.Error("没有获取装载表:" + tableName);
                return null;
            }
        }

    }
}
